CREATE TABLE "privileges"(
 "id" Serial PRIMARY KEY,
 "name" Character varying(45) UNIQUE NOT NULL,
 "created" Timestamp(6) DEFAULT CURRENT_TIMESTAMP NOT NULL
)
WITH (OIDS=FALSE)
;

-- Create indexes for table privileges

CREATE INDEX "privileges created" ON "privileges" ("created" DESC);

-- Table resources

CREATE TABLE "resources"(
 "id" Serial PRIMARY KEY,
 "name" Character varying(45) UNIQUE NOT NULL,
 "created" Timestamp(6) DEFAULT CURRENT_TIMESTAMP NOT NULL
)
WITH (OIDS=FALSE)
;

-- Create indexes for table resources

CREATE INDEX "resources created" ON "resources" ("created" DESC);

-- Table roles

CREATE TABLE "roles"(
 "id" Serial PRIMARY KEY,
 "name" Character varying(45) UNIQUE NOT NULL,
 "parent_id" Integer,
 "created" Timestamp(6) DEFAULT CURRENT_TIMESTAMP NOT NULL
)
WITH (OIDS=FALSE)
;

-- Create indexes for table roles

CREATE INDEX "parent role" ON "roles" ("parent_id");
CREATE INDEX "roles created" ON "roles" ("created" DESC);


-- Table users_roles

CREATE TABLE "users_roles"(
 "user_id" Integer NOT NULL,
 "role_id" Integer NOT NULL
)
WITH (OIDS=FALSE)
;


-- Create indexes for table users_roles

CREATE INDEX "users roles user" ON "users_roles" ("user_id");
CREATE INDEX "users roles role" ON "users_roles" ("role_id");



CREATE TABLE "acl"(
 "id" Serial PRIMARY KEY,
 "role_id" Integer NOT NULL,
 "privilege_id" Integer NOT NULL,
 "resource_id" Integer NOT NULL,
 "allowed" boolean NOT NULL
)
WITH (OIDS=FALSE)
;

-- Create indexes for table acl

CREATE INDEX "acl role" ON "acl" ("role_id");
CREATE INDEX "acl privilege" ON "acl" ("privilege_id");
CREATE INDEX "acl resource" ON "acl" ("resource_id");

-- Add keys for table acl

-- Table acl_log

CREATE TYPE acl_log_action_enum AS ENUM ('insert', 'update', 'delete');

CREATE TABLE "acl_log"(
 "acl_id" Integer,
 "user_id" Integer NOT NULL,
 "time" Timestamp(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
 "action" acl_log_action_enum NOT NULL,
 "role_name" Character varying(45) NOT NULL,
 "resource_name" Character varying(45) NOT NULL,
 "privilege_name" Character varying(45) NOT NULL,
 "allowed" boolean NOT NULL
)
WITH (OIDS=FALSE)
;

-- Create indexes for table acl_log

CREATE INDEX "acl log time" ON "acl_log" ("time");



-- CONSTRAINTS


ALTER TABLE "acl" ADD CONSTRAINT "acl privilege" FOREIGN KEY ("privilege_id") REFERENCES "privileges" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "acl" ADD CONSTRAINT "acl resource" FOREIGN KEY ("resource_id") REFERENCES "resources" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "acl" ADD CONSTRAINT "acl role" FOREIGN KEY ("role_id") REFERENCES "roles" ("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "acl_log" ADD CONSTRAINT "acl log" FOREIGN KEY ("acl_id") REFERENCES "acl" ("id") ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE "roles" ADD CONSTRAINT "parent role" FOREIGN KEY ("parent_id") REFERENCES "roles" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE "users_roles" ADD CONSTRAINT "users roles role" FOREIGN KEY ("role_id") REFERENCES "roles" ("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "users_roles" ADD CONSTRAINT "users roles user" FOREIGN KEY ("user_id") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE;



-- Trigger functions section -------------------------------------------------

CREATE OR REPLACE LANGUAGE plpgsql;

#

CREATE OR REPLACE FUNCTION acl_log_procedure() RETURNS trigger AS $acl_log_procedure$
    DECLARE
        var_old_new RECORD;
        var_id Integer;
        var_user_id Integer;
        var_role_name Character varying(45);
        var_resource_name Character varying(45);
        var_privilege_name Character varying(45);
    BEGIN
        CASE TG_OP
            WHEN 'INSERT', 'UPDATE' THEN
                var_old_new = NEW;
                var_id = NEW."id"; -- we set new ID when triggered after insert or update
            WHEN 'DELETE' THEN
                var_old_new = OLD;
                var_id = NULL; -- we set NULL when triggered after delete. We can't set OLD."id" because of the constraint check
        END CASE;


        SELECT INTO var_user_id "id" FROM "users" NATURAL INNER JOIN (SELECT CURRENT_USER AS user_name) AS current;
        SELECT INTO var_role_name "name" FROM "roles" WHERE "id"=var_old_new."role_id";
        SELECT INTO var_resource_name "name" FROM "resources" WHERE "id"=var_old_new."resource_id";
        SELECT INTO var_privilege_name "name" FROM "privileges" WHERE "id"=var_old_new."privilege_id";

        INSERT INTO acl_log(acl_id, user_id, action, role_name, resource_name,privilege_name,allowed) VALUES(
            var_id, var_user_id, CAST (lower(TG_OP) AS acl_log_action_enum), var_role_name, var_resource_name, var_privilege_name, var_old_new.allowed
        );

        RETURN var_old_new;
    END;
$acl_log_procedure$ LANGUAGE plpgsql;
#


---Triggers section ------------------------------------------------------------------------------------

CREATE TRIGGER acl_log_trigger AFTER INSERT OR UPDATE OR DELETE ON acl
    FOR EACH ROW EXECUTE PROCEDURE acl_log_procedure();
